-- @owner: @zhanghuan96
-- @date: 2024/12/13
-- @testpoint: gms_UTILITY.FORMAT_CALL_STACK多次调用，调用前执行的操作不一样
@conn PrimaryDbAmode;
--前置条件
drop extension if exists gms_utility;
create extension gms_utility;
--step1:建表;expect:成功
drop table if exists tb_format0008_01;
CREATE TABLE tb_format0008_01 (
    log_id        serial,
    log_time      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    operation     VARCHAR2(4000),
    call_stack    CLOB
);
--step2:创建测试表;expect:成功
drop table if exists tb_format0008_02;
create table tb_format0008_02(a int);
--step3:创建存储过程;expect:成功
drop procedure if exists proc_format0008;
CREATE OR REPLACE PROCEDURE proc_format0008(
    IN numerator DOUBLE PRECISION,
    IN denominator DOUBLE PRECISION,
    OUT result DOUBLE PRECISION
)
LANGUAGE plpgsql
AS $$
BEGIN
        result := numerator / denominator;
END;
$$;
/
--step4:执行不同操作调用gms_UTILITY.FORMAT_CALL_STACK函数;expect:成功
DECLARE
    v_call_stack CLOB;
    v_dummy NUMBER;
BEGIN
    SELECT 1 INTO v_dummy;
    v_call_stack := gms_UTILITY.FORMAT_CALL_STACK;
    INSERT INTO tb_format0008_01 (operation, call_stack)
    VALUES ('Executed SELECT INTO on DUAL table', v_call_stack);
    BEGIN
         CALL proc_format0008(10, 0, $1);
    EXCEPTION
        WHEN OTHERS THEN
            v_call_stack := gms_UTILITY.FORMAT_CALL_STACK;
            INSERT INTO tb_format0008_01 (operation, call_stack)
            VALUES ('Called stored procedure ' || SQLERRM, v_call_stack);
    END;
    v_call_stack := gms_UTILITY.FORMAT_CALL_STACK;
    INSERT INTO tb_format0008_01 (operation, call_stack)
    VALUES ('Called stored procedure successfully', v_call_stack);
    INSERT INTO tb_format0008_02 VALUES (1);
    v_call_stack := gms_UTILITY.FORMAT_CALL_STACK;
    INSERT INTO tb_format0008_01 (operation, call_stack)
    VALUES ('Inserted a row into some_table', v_call_stack);
EXCEPTION
    WHEN OTHERS THEN

        ROLLBACK;
        v_call_stack := gms_UTILITY.FORMAT_CALL_STACK;
        INSERT INTO tb_format0008_01 (operation, call_stack)
        VALUES ('Error occurred: ' || SQLERRM, v_call_stack);
END;
/
--step5:查询表;expect:成功
select * from tb_format0008_01;
--step6:清理环境;expect:成功
drop table if exists tb_format0008_01;
drop table if exists tb_format0008_02;
drop procedure if exists proc_format0008;
drop extension if exists gms_utility;